package com.turing.post.salesmanage.impl;

import java.math.BigInteger;
import java.util.List;

import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.salesmanage.util.SaleOrderCardDetail;
import com.turing.post.salesmanage.util.SaleOrderCardDetailDao;
import com.turing.post.util.Config;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;

/**
 * 
 * @author zxp
 * @Dec 23, 2012
 */

@Stateless(mappedName = "salesmanage.impl.SaleOrderCardDetailDaoImpl")
@Remote
@Local
public class SaleOrderCardDetailDaoImpl implements SaleOrderCardDetailDao {
	/**
	 * 实体管理器
	 * 
	 * @see javax.persistence.EntityManager
	 */
	@PersistenceContext
	protected EntityManager _em;

	@Override
	public SaleOrderCardDetail getSaleOrderCardDetailByPrimaryKey(
			SaleOrderCardDetail saleOrderCardDetail) {
		if (saleOrderCardDetail.getSaleOrderId() == null
				|| PCPSUtil.isEmpty(saleOrderCardDetail.getBatchNo())
				|| PCPSUtil.isEmpty(saleOrderCardDetail.getStartCardSeqNo())) {
			return null;
		}
		return _em.find(SaleOrderCardDetail.class, saleOrderCardDetail);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<SaleOrderCardDetail> getSaleOrderCardDetailListBySaleOrderId(
			BigInteger saleOrderId) {
		String sql = "select * from SaleOrderCardDetail t where t.SaleOrderId = ?1 order by batchNo,startCardSeqNo";
		Query query = _em.createNativeQuery(sql, SaleOrderCardDetail.class);
		query.setParameter(1, saleOrderId);
		return query.getResultList();
	}

	@Override
	public void insertSaleOrderCardDetail(
			SaleOrderCardDetail saleOrderCardDetail)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, saleOrderCardDetail);
	}

	@Override
	public Integer deleteSaleOrderCardDetail(
			SaleOrderCardDetail saleOrderCardDetail) {
		try {
			String sql = "delete from SaleOrderCardDetail where saleOrderId = ?1 and BatchNo = ?2 and StartCardSeqNo = ?3";
			Query query = _em.createNativeQuery(sql);
			query.setParameter(1, saleOrderCardDetail.getSaleOrderId());
			query.setParameter(2, saleOrderCardDetail.getBatchNo());
			query.setParameter(3, saleOrderCardDetail.getStartCardSeqNo());
			return query.executeUpdate();
		} catch (Exception e) {
			return -1;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<SaleOrderCardDetail> getSaleOrderCardDetailListByCondition(
			BigInteger saleOrderId, Integer page) {
		String sql = "select * from SaleOrderCardDetail t where t.SaleOrderId = ?1 order by batchNo,startCardSeqNo";
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		Query query = _em.createNativeQuery(sql, SaleOrderCardDetail.class)
				.setFirstResult(startPosition).setMaxResults(maxResult);
		query.setParameter(1, saleOrderId);
		return query.getResultList();
	}

	@Override
	public BigInteger getSaleOrderCardDetailTotalByCondition(
			BigInteger saleOrderId) {
		String sql = "select nvl(count(*),0) count from SaleOrderCardDetail t where t.SaleOrderId = ?1";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, saleOrderId);
		return new BigInteger(query.getSingleResult().toString());
	}

	@Override
	public SaleOrderCardDetail getSaleOrderCardDetailByBatchNo(String batchNo,
			String startCardSeqNo, String endCardSeqNo) {
		String sql = "select * from SaleOrderCardDetail t where t.BatchNo = ?1 "
				+ " and t.StartCardSeqNo = ?2 and t.EndCardSeqNo = ?3 order by batchNo,startCardSeqNo";
		Query query = _em.createNativeQuery(sql, SaleOrderCardDetail.class);
		query.setParameter(1, batchNo);
		query.setParameter(2, startCardSeqNo);
		query.setParameter(3, endCardSeqNo);
		return (SaleOrderCardDetail) query.getSingleResult();
	}

	@SuppressWarnings("unchecked")
	@Override
	public String[] getSaleOrderByCardSeqNo(String cardSeqNo) {
		String sql = "select t.PersonalBuyerId,t.OrganizeBuyerId,t.saleOrderId from SaleOrderCardDetail s,saleorder t where s.saleorderid=t.saleorderid and t.CancelOperId is null and ?1 between s.startcardseqno and s.endcardseqno";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, cardSeqNo);
		List<Object[]> objList = query.getResultList();
		if (objList == null || objList.size() < 1) {
			return null;
		}
		String[] st = new String[] { objList.get(0)[0] + "",
				objList.get(0)[1] + "", objList.get(0)[2] + "" };
		return st;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getSaleOrderCardDetailReportBySaleOrderId(
			BigInteger saleOrderId) {
		String sql = "select t.startcardseqno||'至'||t.endcardseqno cardseqno,c.cardclassicname,"
				+ "case when f.mediatype= '0' then '磁卡' "
				+ "when f.mediatype= '1' then 'CPU多界面卡' end as mediatype,"
				+ "t.cardcount,t.initdeposit,t.orderlinetotal "
				+ "from saleordercarddetail t,makecardcontrol m,cardclassic c,cardface f "
				+ "where t.cardclassicid=c.cardclassicid and t.batchno=m.batchno "
				+ "and m.cardfaceid=f.cardfaceid and t.saleorderid="
				+ saleOrderId
				+ " order by t.startcardseqno,t.cardclassicid,t.batchno";
		Query query = _em.createNativeQuery(sql);
		return query.getResultList();
	}

}
